package dataGateway;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import domainLogic.ItemView;

/**
 * @author  Tim Moon
 */
public class ItemViewTable {


	private static ItemViewTable instance = new ItemViewTable();
	
	public static ItemViewTable getInstance()
	{
		return instance;
	}
		
	public ItemViewTable() {  }
	
	private Connection getConnection() throws Exception {
		return ConnectionPool.getInstance().getConnection();
	} //getConnection()

	public List<ItemView> getItemSql(String sqlWhere)
	{
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<ItemView> ret = new ArrayList<ItemView>();
		
		try {
			conn = getConnection();
			String sql = "select auctionview.*, categories.name as cateogory_name from auctionview, categories where categories.id = cat_id ";
			if(sqlWhere != null)
			{
				sql += sqlWhere;
			}
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) 
			{
				ret.add(new ItemView(rs));
			} 
		} catch(Exception ex) {
					ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(stmt != null) try { stmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("getItemSql: "+(System.currentTimeMillis()-startTime));
		return ret;
	}
	
	public int getNumItems(String sqlWhere) 
	{
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		int ret = 0;
		
		try {
			conn = getConnection();
			String sql = "select count(id) as count from auctionview where true ";
			
			if(sqlWhere != null)
				sql += sqlWhere;
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			if(rs.next()) 
			{
				ret = rs.getInt("count");
			} 
		} catch(Exception ex) {
					ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(stmt != null) try { stmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("getNumItems: "+(System.currentTimeMillis()-startTime));
	
		return ret;
	}
	
	public ItemView getItem(int id){
		List<ItemView> items = getItemSql("and auctionview.id=" + id);
		
		if(items != null && items.size() > 0)
			return items.get(0);
		
		return null;
	}

	//Completed auction for registered users	
	private String makeSqlWhereStatement(
			Boolean isCompleted,
			String seller, 
			String kwd, 
			String winner) 
	{		
		String sql = null;
		
		if(isCompleted != null)
		{
			if(isCompleted == true)
				sql = " and realenddate <= CURRENT_TIMESTAMP ";
			else
				sql = " and realenddate > CURRENT_TIMESTAMP ";
		}
		
		if (winner != null && !winner.equals(""))  //auction user won
			sql += " and reserveprice <= highest_price " +
					" and highest_bidder='" + winner + "'";	
		if (seller != null && !seller.equals("")) 
			sql += " and user_id='" + seller + "'";
		if (kwd != null && !kwd.equals("")) 
		    sql += " and (lower(auctionview.name) like '%" + kwd + "%' or lower(description) like '%" + kwd + "%')" ;
		return sql;
	}
	
	public List<ItemView>  getItemView(
			boolean isCompleted,
			String seller, 
			String kwd, 
			String winner,
			boolean orderByPrice, int start, int max) throws Exception 
	{
		String sql = makeSqlWhereStatement(isCompleted, seller, kwd, winner);
		String orderBy = null;
		
		if(orderByPrice)
			orderBy = "highest_price desc, realenddate asc ";
		else
			orderBy = "realenddate asc , highest_price desc ";
		
		sql += " ORDER BY "+orderBy+" OFFSET " + start + " ROWS FETCH NEXT " + max + " ROWS ONLY ";
		
		return getItemSql(sql);
	}

	//Completed auction for registered users
	public int getNumItemView(
			boolean isCompleted,
			String seller, 
			String kwd, 
			String winner) throws Exception 
	{	
		String sql = makeSqlWhereStatement(isCompleted, seller, kwd, winner);
		return getNumItems(sql);
	}
}
